﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ChequeManagement.DAL
{
    /// <summary>
    /// this class contain methods for connect to dataBase 
    /// this methods are Insert,Update,FetchAll,Delete,InitialObjectsFromReader,InitialBusinessObjectsFromReader
    /// this class inheritance of BaseDAL
    /// </summary>
    class DALChequeCategory : BaseDAL
    {

        /// <summary>
        /// this method is for insert a object of BLL.BLLBank to table
        /// this method check connection if connection is open insert object to table
        /// </summary>
        /// <param name="chequeCategoryObject" Type="BLL.BLLChequeCategory"></param>
        /// <return>this type is void and Do not return</return>
        public void Insert(BLL.BLLChequeCategory chequeCategoryObject)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryInsert";
            this.Command.CommandType = CommandType.StoredProcedure;

            //try
            //{

                this.Command.Parameters.Clear();
                #region SQL Parameters
                SqlParameter bankID = new SqlParameter();
                bankID.ParameterName = "@BankID";
                bankID.SqlDbType = SqlDbType.Int;
                bankID.Direction = ParameterDirection.Input;
                bankID.IsNullable = false;
                bankID.Value = chequeCategoryObject.BankObject.BankID;
                this.Command.Parameters.Add(bankID);

                SqlParameter accountNo = new SqlParameter();
                accountNo.ParameterName = "@AccountNo";
                accountNo.SqlDbType = SqlDbType.BigInt;
                accountNo.Direction = ParameterDirection.Input;
                accountNo.IsNullable = false;
                accountNo.Value = chequeCategoryObject.AccountNo;
                this.Command.Parameters.Add(accountNo);

                SqlParameter branchName = new SqlParameter();
                branchName.ParameterName = "@BranchName";
                branchName.SqlDbType = SqlDbType.NVarChar;
                branchName.Direction = ParameterDirection.Input;
                branchName.IsNullable = false;
                branchName.Value = chequeCategoryObject.BranchName;
                this.Command.Parameters.Add(branchName);

                SqlParameter chequeCategoryID = new SqlParameter();
                chequeCategoryID.ParameterName = "@ChequeCategoryID";
                chequeCategoryID.SqlDbType = SqlDbType.Int;
                chequeCategoryID.Direction = ParameterDirection.Output;
                chequeCategoryID.IsNullable = true;
                this.Command.Parameters.Add(chequeCategoryID);

                SqlParameter chequeCategoryName = new SqlParameter();
                chequeCategoryName.ParameterName = "@ChequeCategoryName";
                chequeCategoryName.SqlDbType = SqlDbType.NVarChar;
                chequeCategoryName.Direction = ParameterDirection.Input;
                chequeCategoryName.IsNullable = false;
                chequeCategoryName.Value = chequeCategoryObject.ChequeCategoryName;
                this.Command.Parameters.Add(chequeCategoryName);

                SqlParameter firstPageNo = new SqlParameter();
                firstPageNo.ParameterName = "@FirstPageNo";
                firstPageNo.SqlDbType = SqlDbType.BigInt;
                firstPageNo.Direction = ParameterDirection.Input;
                firstPageNo.IsNullable = false;
                firstPageNo.Value = chequeCategoryObject.FirstPageNo;
                this.Command.Parameters.Add(firstPageNo);

                SqlParameter pageCount = new SqlParameter();
                pageCount.ParameterName = "@PageCount";
                pageCount.SqlDbType = SqlDbType.SmallInt;
                pageCount.Direction = ParameterDirection.Input;
                pageCount.IsNullable = false;
                pageCount.Value = chequeCategoryObject.PageCount;
                this.Command.Parameters.Add(pageCount);

              
                #endregion

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                //chequeCategoryObject.ChequeCategoryID = Convert.ToInt32(this.Command.Parameters["@ChequeCategoryID"].Value);

                if (controlConnection)
                    this.CommitTransaction();
            //}
            //catch (Exception ex)
            //{
            //    if (controlConnection)
            //        this.RollBackTransaction();
            //    throw (ex);
            //}
        }

        /// <summary>
        /// this method is for Update a object of BLL.BLLBank to table by ChequeCategoryID
        /// this method check connection if connection is open Update object to table
        /// </summary>
        /// <param name="chequeCategoryObject" Type="BLL.BLLChequeCategory"></param>
        /// <param name="targetChequeCategroyID" Type="int"></param>
        ///  <return>this type is void and Do not return</return>
        public void Update(BLL.BLLChequeCategory chequeCategoryObject, int targetChequeCategroyID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryUpdate";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                #region SQL Parameters
                SqlParameter accountNo = new SqlParameter();
                accountNo.ParameterName = "@AccountNo";
                accountNo.SqlDbType = SqlDbType.BigInt;
                accountNo.Direction = ParameterDirection.Input;
                accountNo.IsNullable = false;
                accountNo.Value = chequeCategoryObject.AccountNo;
                this.Command.Parameters.Add(accountNo);

                SqlParameter branchName = new SqlParameter();
                branchName.ParameterName = "@BranchName";
                branchName.SqlDbType = SqlDbType.NVarChar;
                branchName.Direction = ParameterDirection.Input;
                branchName.IsNullable = false;
                branchName.Value = chequeCategoryObject.BranchName;
                this.Command.Parameters.Add(branchName);

                SqlParameter chequeCategoryID = new SqlParameter();
                chequeCategoryID.ParameterName = "@ChequeCategoryID";
                chequeCategoryID.SqlDbType = SqlDbType.Int;
                chequeCategoryID.Direction = ParameterDirection.Input;
                chequeCategoryID.IsNullable = false;
                chequeCategoryID.Value = chequeCategoryObject.ChequeCategoryID;
                this.Command.Parameters.Add(chequeCategoryID);

                SqlParameter chequeCategoryName = new SqlParameter();
                chequeCategoryName.ParameterName = "@ChequeCategoryName";
                chequeCategoryName.SqlDbType = SqlDbType.NVarChar;
                chequeCategoryName.Direction = ParameterDirection.Input;
                chequeCategoryName.IsNullable = false;
                chequeCategoryName.Value = chequeCategoryObject.ChequeCategoryName;
                this.Command.Parameters.Add(chequeCategoryName);

                SqlParameter firstPageNo = new SqlParameter();
                firstPageNo.ParameterName = "@FirstPageNo";
                firstPageNo.SqlDbType = SqlDbType.BigInt;
                firstPageNo.Direction = ParameterDirection.Input;
                firstPageNo.IsNullable = false;
                firstPageNo.Value = chequeCategoryObject.FirstPageNo;
                this.Command.Parameters.Add(firstPageNo);

                SqlParameter pageCount = new SqlParameter();
                pageCount.ParameterName = "@PageCount";
                pageCount.SqlDbType = SqlDbType.SmallInt;
                pageCount.Direction = ParameterDirection.Input;
                pageCount.IsNullable = false;
                pageCount.Value = chequeCategoryObject.PageCount;
                this.Command.Parameters.Add(pageCount);

                SqlParameter bankID = new SqlParameter();
                bankID.ParameterName = "@BankID";
                bankID.SqlDbType = SqlDbType.Int;
                bankID.Direction = ParameterDirection.Input;
                bankID.IsNullable = false;
                bankID.Value = chequeCategoryObject.BankObject.BankID;
                this.Command.Parameters.Add(bankID);
                #endregion

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="chequeCategoryDT"></param>
        /// <return>this type is void but result return to DataTable </return>
        public void FetchAll(DataTable chequeCategoryDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspChequeCategoryFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                sqlDataAdapter.Fill(chequeCategoryDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <return>result return to a list<> of ChequeManagement.BLL.BLLChequeCategory</return>
        public List<ChequeManagement.BLL.BLLChequeCategory> FetchAll()
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspChequeCategoryFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                //we must close the datareader because if we have a exception and the roolBack happen it will cause an error.
                //because it cannat roolback if a dataReader was open
                List<BLL.BLLChequeCategory> result = new List<BLL.BLLChequeCategory>();
                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <returns>result return to a list<> of BLL.BLLChequeCategory</returns>
        public List<BLL.BLLChequeCategory> FetchByField(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[ChequeCategory].[" + fieldName + "] = N'" + value.ToString() + "'"));

                
                List<BLL.BLLChequeCategory> result = new List<BLL.BLLChequeCategory>();
                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <param name="chequeCategoryDT" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchByField(string fieldName, object value, DataTable chequeCategoryDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspChequeCategoryFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();



                this.Command.Parameters.Add(new SqlParameter("@condition", "[ChequeCategory].[" + fieldName + "] = N'" + value.ToString() + "'"));

                sqlDataAdapter.Fill(chequeCategoryDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by condition 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="condition" Type="string"></param>
        /// <param name="chequeCategoryDT" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchAllByCondition(string condition, DataTable chequeCategoryDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspChequeCategoryFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();


                this.Command.Parameters.Add(new SqlParameter("@condition", condition));

                sqlDataAdapter.Fill(chequeCategoryDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by ChequeCategoryID 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="ChequeCategoryID" Type="int"></param>
        /// <returns>return a object that type is BLL.BLLChequeCategory</returns>
        public BLL.BLLChequeCategory FetchByKey(int ChequeCategoryID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryFetchByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter chequeCategoryID = new SqlParameter();
                chequeCategoryID.ParameterName = "@ChequeCategoryID";
                chequeCategoryID.SqlDbType = SqlDbType.Int;
                chequeCategoryID.Direction = ParameterDirection.Input;
                chequeCategoryID.IsNullable = false;
                chequeCategoryID.Value = ChequeCategoryID;
                this.Command.Parameters.Add(chequeCategoryID);

                if (controlConnection)
                    this.BeginTransaction();


                BLL.BLLChequeCategory chequeCategoryObject = new BLL.BLLChequeCategory();

                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    if (dataReader.Read())
                        InitialBusinessObjectsFromReader(chequeCategoryObject, dataReader);
                    else
                        chequeCategoryObject = null;
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (chequeCategoryObject);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Delete a row from table
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="ChequeCategoryID" Type="int"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(int ChequeCategoryID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryDeleteByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter chequeCategoryID = new SqlParameter();
                chequeCategoryID.ParameterName = "@ChequeCategoryID";
                chequeCategoryID.SqlDbType = SqlDbType.Int;
                chequeCategoryID.Direction = ParameterDirection.Input;
                chequeCategoryID.IsNullable = false;
                chequeCategoryID.Value = ChequeCategoryID;
                this.Command.Parameters.Add(chequeCategoryID);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Delete a row from table by field name that field name value is this value
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspDeleteByField";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                this.Command.Parameters.Add(new SqlParameter("@fieldName", fieldName));
                this.Command.Parameters.Add(new SqlParameter("@value", value));

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>return a list<> that value is table columns value</returns>
        private List<BLL.BLLChequeCategory> InitialObjectsFromReader(IDataReader dataReader)
        {
            List<BLL.BLLChequeCategory> chequeCategoryList = new List<BLL.BLLChequeCategory>();

            int chequeCategoryIDColumn = dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.ChequeCategoryID.ToString());
            int chequeCategoryNameColumn = dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.ChequeCategoryName.ToString());
            int AccountNoColumn = dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.AccountNo.ToString());
            int BranchNameColumn = dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.BranchName.ToString());
            int FirstPageNoColumn = dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.FirstPageNo.ToString());
            int PageCountColumn = dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.PageCount.ToString());
            int BankIDColumn = dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.BankID.ToString());

            while (dataReader.Read())
            {
                BLL.BLLChequeCategory ChequeCategoryObject = new BLL.BLLChequeCategory();

                ChequeCategoryObject.ChequeCategoryID = dataReader.GetInt32(chequeCategoryIDColumn);
                ChequeCategoryObject.ChequeCategoryName = dataReader.GetString(chequeCategoryNameColumn);
                ChequeCategoryObject.AccountNo = dataReader.GetInt64(AccountNoColumn);
                ChequeCategoryObject.BranchName = dataReader.GetString(BranchNameColumn);
                ChequeCategoryObject.FirstPageNo = dataReader.GetInt64(FirstPageNoColumn);
                ChequeCategoryObject.PageCount = dataReader.GetInt16(PageCountColumn);
                ChequeCategoryObject.BankID = dataReader.GetInt32(BankIDColumn);

                chequeCategoryList.Add(ChequeCategoryObject);
            }
            return (chequeCategoryList);
        }

        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="ChequeCategory" Type="BLL.BLLChequeCategory"></param>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>this method is void but return a object of BLL.BLLChequeCategory that value is table columns value</returns>
        private void InitialBusinessObjectsFromReader(BLL.BLLChequeCategory ChequeCategory, IDataReader dataReader)
        {
            ChequeCategory.ChequeCategoryID = dataReader.GetInt32(dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.ChequeCategoryID.ToString()));
            ChequeCategory.ChequeCategoryName = dataReader.GetString(dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.ChequeCategoryName.ToString()));
            ChequeCategory.AccountNo = dataReader.GetInt64(dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.AccountNo.ToString()));
            ChequeCategory.BranchName = dataReader.GetString(dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.BranchName.ToString()));
            ChequeCategory.FirstPageNo = dataReader.GetInt64(dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.FirstPageNo.ToString()));
            ChequeCategory.PageCount = dataReader.GetInt32(dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.PageCount.ToString()));
            ChequeCategory.BankID = dataReader.GetInt32(dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.BankID.ToString()));
        }
    }
}
